package common.dal;

import java.sql.*;
import java.util.*;

import common.Pair;
import common.dal.SQLDataColumn.Constraints;
import common.extensions.Extender;
import common.interfaces.IPersistent;
import common.logger.*;
import common.logger.Logger.LogLevel;

/**
 * An abstract implementation of {@link IPersistent}
 * 
 * @author Gal Lalouche
 */
public abstract class AbstractPersistent implements IPersistent {
	// private final String url;
	// private final String dbName;
	// private final String username;
	// private final String password;
	private final int			rowsCount;
	
	static String				SELECT_ALL	= "*";
	Logger						log			= CompositeLogger.getInstance();
	// if null, connection is closed
	private final Connection	connection;
	
	/**
	 * The transaction all actions and query must act upon
	 */
	protected final Transaction	t;
	
	// // opens a new connection if one is not already open
	// boolean openConnection() throws SQLException {
	// if (connection != null) {
	// return false;
	// }
	//
	// connection = DriverManager.getConnection(url + dbName, username, password);
	// log.log(LogLevel.DEBUG, "opening connection of " + getClass().getCanonicalName());
	// return true;
	// }
	//
	// // closes the connection if it is not closed
	// boolean closeConnection() throws SQLException {
	// if (connection == null) {
	// return false;
	// }
	//
	// connection.close();
	// connection = null;
	// log.log(LogLevel.DEBUG, "closing connection of " + getClass().getCanonicalName());
	// return true;
	// }
	
	// returns true if the table already exists
	// private boolean doesTableExist() throws SQLException {
	// boolean exists = select("show tables like '" + (getTableName()) + "'").first();
	// return exists;
	// }
	
	// creates the string of the foreign key constraint
	private static String createForeignKey(SQLDataColumn type) {
		assert type.getConstraints().contains(Constraints.FOREIGN_KEY);
		return Constraints.FOREIGN_KEY + "(" + type.getName() + ") REFERENCES "
				+ ((Pair<?, ?>)type.getExtraData()).first + "(" + ((Pair<?, ?>)type.getExtraData()).second + "), ";
	}
	
	// append the where clauses and creates a prepared statement
	private PreparedStatement appendWheres(Iterable<? extends WhereEntry> entries, StringBuilder sb)
			throws SQLException {
		int length = Extender.count(entries);
		if (length > 0) {
			sb.append(" WHERE ");
		}
		Iterator<? extends WhereEntry> iterator = entries.iterator();
		for (int i = 0; i < length; i++) {
			WhereEntry triplet = iterator.next();
			sb.append(triplet.first + " " + triplet.second + " ? ");
			if (i < (length - 1)) {
				sb.append(" AND ");
			}
		}
		PreparedStatement selectStatement = connection.prepareStatement(sb.toString());
		iterator = entries.iterator();
		for (int i = 1; i <= length; i++) {
			WhereEntry triplet = iterator.next();
			selectStatement.setObject(i, triplet.third);
		}
		return selectStatement;
	}
	
	// executes the give query
	private void execute(String query) throws SQLException {
		// openConnection();
		try (Statement statement = connection.createStatement()) {
			log.log(Logger.LogLevel.VERBOSE, query);
			statement.execute(query);
		}
	}
	
	// Executes the given query and returns all results
	// @SuppressWarnings("resource")
	// private ResultSet select(String query) throws SQLException {
	// // openConnection();
	// Statement statement = connection.createStatement();
	// log.log(Logger.LogLevel.VERBOSE, query);
	// ResultSet result = statement.executeQuery(query);
	// // connection.commit();
	// return result;
	// }
	
	// creates the table, if does not exist
	private void createTable() throws SQLException {
		StringBuilder createQuery = new StringBuilder();
		createQuery.append("CREATE TABLE IF NOT EXISTS " + getTableName() + "(");
		SQLDataColumn[] dataTypes = getTableColumns();
		for (int i = 0; i < dataTypes.length; i++) {
			SQLDataColumn type = dataTypes[i];
			if (type.getConstraints().contains(Constraints.FOREIGN_KEY)) {
				createQuery.append(createForeignKey(type));
			}
			createQuery.append(type.getName() + " " + type.getSQLDataType());
			// if (type.getType() == Types.VARCHAR) {
			// createQuery.append("(" + type.getExtraData() + ")");
			// }
			for (SQLDataColumn.Constraints constraint: type.getConstraints()) {
				if (constraint == Constraints.INDEX) {
					createQuery.append(", INDEX(" + type.getName() + ")");
					continue;
				}
				if (constraint == Constraints.FOREIGN_KEY) {
					// already handled above
					continue;
				}
				createQuery.append(" " + constraint);
				if (constraint == Constraints.DEFAULT) {
					createQuery.append(" " + type.getExtraData());
				}
			}
			if (i < (dataTypes.length - 1)) {
				createQuery.append(", ");
			}
		}
		createQuery.append(")");
		execute(createQuery.toString());
	}
	
	@Override
	protected void finalize() throws Throwable {
		// closeConnection();
		super.finalize();
	}
	
	/**
	 * Creates a new Persistent
	 * 
	 * @param t The transaction all actions will act upon
	 */
	protected AbstractPersistent(Transaction t) {
		rowsCount = getTableColumns().length;
		connection = t.getConnection();
		this.t = t;
	}
	
	/**
	 * A template of the column names used for table creation
	 * 
	 * @return An array of the column data to create the table based on
	 */
	protected abstract SQLDataColumn[] getTableColumns();
	
	/**
	 * A template of the table name
	 * 
	 * @return The table name
	 */
	protected abstract String getTableName();
	
	/**
	 * @param entries The entries of the where clause
	 * @return {@code true} iff any rows match the where clause (AND between all clauses)
	 * @throws SQLException
	 */
	protected boolean existsWhere(Iterable<? extends WhereEntry> entries) throws SQLException {
		return selectWhere(entries).first();
	}
	
	/**
	 * @return All the rows in the database
	 * @throws SQLException
	 */
	protected ResultSet selectAll() throws SQLException {
		return selectWhere(new LinkedList<WhereEntry>());
	}
	
	/**
	 * @param entry The where clause entry
	 * @return All rows satisfying the entry
	 * @throws SQLException
	 */
	protected ResultSet selectWhere(WhereEntry entry) throws SQLException {
		return selectWhere(Extender.toCollection(entry));
	}
	
	/**
	 * @param entries The entries of the where clause
	 * @return All rows satisfying the where clauses (AND between all clauses)
	 * @throws SQLException
	 */
	@SuppressWarnings("resource")
	protected ResultSet selectWhere(Iterable<? extends WhereEntry> entries) throws SQLException {
		StringBuilder sb = new StringBuilder("SELECT * FROM " + getTableName());
		PreparedStatement selectStatement = appendWheres(entries, sb);
		log.log(LogLevel.VERBOSE, selectStatement.toString());
		ResultSet set = selectStatement.executeQuery();
		// connection.commit();
		return set;
	}
	
	/**
	 * @param entry The where clause entry
	 * @return The number of rows satisfying the where clause
	 * @throws SQLException
	 */
	protected int countWhere(WhereEntry entry) throws SQLException {
		return countWhere(Extender.toCollection(entry));
	}
	
	/**
	 * @param entries The entries to match against. only rows matching will be counted
	 * @return The number of rows satisfying the where clause
	 * @throws SQLException
	 */
	protected int countWhere(Iterable<? extends WhereEntry> entries) throws SQLException {
		StringBuilder sb = new StringBuilder("SELECT count(*) FROM " + getTableName());
		PreparedStatement selectStatement = appendWheres(entries, sb);
		try (ResultSet set = selectStatement.executeQuery()) {
			boolean result = set.first();
			assert result;
			return set.getInt(1);
		}
	}
	
	/**
	 * Inserts a row to the database
	 * 
	 * @param values The value to insert; if {@code values.length < getTableColumns().length} all extra rows will
	 *            default
	 * @throws ArithmeticException if {@code value.length > getTableColumns().length}
	 * @throws SQLException
	 */
	protected void insertInto(Object[] values) throws SQLException {
		if (values.length > rowsCount) {
			throw new ArithmeticException("values is too big; values: " + values.length + ", rows: " + rowsCount);
		}
		
		StringBuilder insertQuery = new StringBuilder();
		insertQuery.append("INSERT INTO " + getTableName() + " VALUES (");
		
		// generate statement
		for (int i = 0; i < rowsCount; i++) {
			if (i < values.length) {
				insertQuery.append("?");
			} else {
				// default for extra values
				insertQuery.append("DEFAULT");
			}
			if (i < (rowsCount - 1)) {
				insertQuery.append(", ");
			}
		}
		insertQuery.append(")");
		// openConnection();
		try (PreparedStatement insertStatement = connection.prepareStatement(insertQuery.toString())) {
			// insert values to statement
			for (int i = 0; i < values.length; i++) {
				insertStatement.setObject(i + 1, values[i]);
			}
			log.log(Logger.LogLevel.VERBOSE, insertStatement.toString());
			insertStatement.execute();
		}
	}
	
	/**
	 * Deletes all rows satisfying a condition
	 * 
	 * @param entry The entry to delete
	 * @throws SQLException
	 */
	protected void deleteWhere(WhereEntry entry) throws SQLException {
		deleteWhere(Extender.toCollection(entry));
	}
	
	/**
	 * Deletes all rows, satisfying multiple conditions
	 * 
	 * @param entries The pair<b>s</b> of the where clause, in the format of (column, value)
	 * @throws SQLException
	 */
	protected void deleteWhere(Iterable<? extends WhereEntry> entries) throws SQLException {
		StringBuilder sb = new StringBuilder("DELETE FROM " + getTableName());
		try (PreparedStatement statement = appendWheres(entries, sb)) {
			statement.execute();
		}
	}
	
	/**
	 * @param columnName The name of column to max
	 * @return The max value of the column
	 * @throws SQLException
	 */
	@SuppressWarnings("resource")
	protected ResultSet max(String columnName) throws SQLException {
		PreparedStatement ps = connection
				.prepareStatement("SELECT MAX(" + columnName + ") FROM " + getTableName());
		log.log(LogLevel.VERBOSE, ps.toString());
		ResultSet result = ps.executeQuery();
		connection.commit();
		return result;
	}
	
	@Override
	public boolean drop() throws SQLException {
		try {
			execute("DROP TABLE IF EXISTS " + getTableName());
			return true;
		} finally {
			// closeConnection();
		}
	}
	
	@Override
	public void clear() throws SQLException {
		execute("DELETE FROM " + getTableName());
	}
	
	@Override
	public void setup() throws SQLException {
		drop();
		createTable();
	}
	
	// @Override
	// public boolean setTransaction(Transaction t) throws SQLException {
	// if (t == null) {
	// throw new IllegalArgumentException("t is null");
	// }
	// if (t.connection.isClosed()) {
	// throw new IllegalStateException("Transaction's connection has been closed");
	// }
	// if (this.connection != null) {
	// log.log(LogLevel.DEBUG, "connection already exists, ignoring setTransaction from "
	// + t.getCallingMethod());
	// return false;
	// }
	// connection = t.connection;
	// t.addUsingDB(this);
	// return true;
	// }
	
	// /**
	// * Sets the connection to null
	// */
	// void stopUsingTransaction() {
	// log.log(LogLevel.DEBUG, "setting connection to null");
	// assert connection != null;
	// connection = null;
	// }
}
